package report;

import org.apache.log4j.Logger;

import java.*;
import java.sql.Driver;

import org.apache.struts.config.ControllerConfig;
public class ConnectSqlServer {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(ConnectSqlServer.class);

	 private static java.sql.Connection  con = null;
   private static final String url = "jdbc:sqlserver://";
   private static final String serverName= "localhost11";
   private static final String portNumber = "143311";
   private static final String databaseName= "sg_market1011";
   private static final String userName = "sa";
   private static final String password = "sa123456";
   
   // Informs the driver to use server a side-cursor, 
   // which permits more than one active statement 
   // on a connection.
   private static final String selectMethod = "cursor"; 
   
   // Constructor
   public ConnectSqlServer(){
  	 
  	 
   }
   
   private static String getConnectionUrl(){
		if (logger.isDebugEnabled()) {
			logger.debug("getConnectionUrl() - start");
		}

//        return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
		String returnString = ConConfig.getInst().getUrl();
		if (logger.isDebugEnabled()) {
			logger.debug("getConnectionUrl() - end");
		}
  	 		return returnString;
   }
   
   public static java.sql.Connection getConnection(){
		if (logger.isDebugEnabled()) {
			logger.debug("getConnection() - start");
		}

        try{
        		if(null==ConConfig.getInst().getUrl())
        			ConConfig.getInst().init();
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
             String surl = getConnectionUrl();
             //System.out.println(surl);
             con = java.sql.DriverManager.getConnection(surl,ConConfig.getInst().getUserName(),ConConfig.getInst().getPassword());
             //if(con!=null) System.out.println("Connection Successful!");
        }catch(Exception e){
			logger.error("getConnection()", e);

             e.printStackTrace();
//             System.out.println("Error Trace in getConnection() : " + e.getMessage());
       }

		if (logger.isDebugEnabled()) {
			logger.debug("getConnection() - end");
		}
        return con;
    }

   /*
        Display the driver properties, database details 
   */ 

   public void displayDbProperties(){
		if (logger.isDebugEnabled()) {
			logger.debug("displayDbProperties() - start");
		}

        java.sql.DatabaseMetaData dm = null;
        java.sql.ResultSet rs = null;
        try{
             con= this.getConnection();
             if(con!=null){
                  dm = con.getMetaData();
//                  System.out.println("Driver Information");
//                  System.out.println("\tDriver Name: "+ dm.getDriverName());
//                  System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
//                  System.out.println("\nDatabase Information ");
//                  System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
//                  System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
//                  System.out.println("Avalilable Catalogs ");
                  logger.debug("Driver Information");
                  logger.debug("\tDriver Name: "+ dm.getDriverName());
                  logger.debug("\tDriver Version: "+ dm.getDriverVersion ());
                  logger.debug("\nDatabase Information ");
                  logger.debug("\tDatabase Name: "+ dm.getDatabaseProductName());
                  logger.debug("\tDatabase Version: "+ dm.getDatabaseProductVersion());
                  logger.debug("Avalilable Catalogs ");
                  rs = dm.getCatalogs();
                  while(rs.next()){
                       //System.out.println("\tcatalog: "+ rs.getString(1));
                       logger.debug("\tcatalog: "+ rs.getString(1));
                  } 
                  rs.close();
                  rs = null;
                  closeConnection();
             }else System.out.println("Error: No active Connection");
        }catch(Exception e){
			logger.error("displayDbProperties()", e);

             e.printStackTrace();
        }
        dm=null;

		if (logger.isDebugEnabled()) {
			logger.debug("displayDbProperties() - end");
		}
   }     
   
   private void closeConnection(){
		if (logger.isDebugEnabled()) {
			logger.debug("closeConnection() - start");
		}

        try{
             if(con!=null)
                  con.close();
             con=null;
        }catch(Exception e){
			logger.error("closeConnection()", e);

             e.printStackTrace();
        }

		if (logger.isDebugEnabled()) {
			logger.debug("closeConnection() - end");
		}
   }
   public static void main(String[] args) throws Exception
     {
		if (logger.isDebugEnabled()) {
			logger.debug("main(String[]) - start");
		}

  	   //Driver d = (Driver)Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
  	   //System.out.println("d="+d);
  	 		ConnectSqlServer myDbTest = new ConnectSqlServer();
        myDbTest.displayDbProperties();

		if (logger.isDebugEnabled()) {
			logger.debug("main(String[]) - end");
		}
     }
}
